package com.mxpio.erp.equipment.service.impl;


import com.mxpio.erp.common.equipment.service.EquipmentReportService;
import com.mxpio.erp.common.equipment.vo.EquipmentReportVO;
import com.mxpioframework.dbconsole.DbConstants;
import com.mxpioframework.dbconsole.model.DataGridWrapper;
import com.mxpioframework.dbconsole.service.DbService;
import com.mxpioframework.jpa.JpaUtil;
import com.mxpioframework.security.entity.Dept;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class EquipmentReportServiceImpl implements EquipmentReportService {

	@Autowired
	private DbService dbService;

	@Value("${pmis.dbInfoId:}")
	private String dbInfoId;

	@Autowired
	private JdbcTemplate jdbcTemplate;


	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getMaintenanceHours() {
		String sql = "SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))\n" +
				" UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))\n" +
				"\t UNION\n" +
				"SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))\n" +
				"\t UNION\n" +
				" SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))\n" +
				" ";
		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}


	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getUpkeepHours() {
		String sql = "SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))\n" +
				" UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))\n" +
				"  ";
		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getUpkeepCompletionRate() {
		String sql = "select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))))as mValue)as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getCheckCompletionRate() {
		String sql = "select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))))as mValue)as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getAuditCompletionRate() {
		String sql = "select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 5 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 5 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 5 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 4 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 4 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 4 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 0 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 0 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 0 MONTH)) ) ) as mValue ) as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getRepairCompletionRate() {
		String sql = "select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))))as mValue)as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentFailure() {
		String sql = "select abc_type_ as m,count(id_) as value from mb_erp_repair GROUP BY abc_type_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentStatus() {
		String sql = "select CASE status_ WHEN 0 THEN '购置' WHEN 10 THEN '验收' WHEN 20 THEN '安装'WHEN 30 THEN '使用中'WHEN 40 THEN '维保中'WHEN 50 THEN '停机' WHEN 70 THEN '报废' END as xx ,count(id_) as value from mb_erp_equipment_eqp_basics GROUP BY status_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentNormalStatus() {
		String sql = "SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END as value from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 5 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_<DATE_SUB( CURDATE( ), INTERVAL 4 MONTH )) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 5 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 4 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_<DATE_SUB( CURDATE( ), INTERVAL 3 MONTH )) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 4 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 3 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL 2 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 3 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 2 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 2 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL 0 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 0 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL -1 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 0 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentFailureCount() {
		String sql = "SELECT eb.eqp_code_, eb.eqp_name_, count(er.id_) as value, SUM(TIMESTAMPDIFF(MINUTE,er.create_time,IFNULL(er.next_operate_time_,NOW()))) as timeValue FROM mb_erp_equipment_eqp_status_records er LEFT JOIN mb_erp_equipment_eqp_basics eb ON er.eqp_id_ = eb.id_ WHERE er.now_status_ = 40 GROUP BY er.eqp_id_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertEqpResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getMeanTimeBeforeFailure() {
		String sql = "SELECT eb.eqp_code_, eb.eqp_name_, count(er.id_) as value, (SUM(TIMESTAMPDIFF(MINUTE,er.create_time,IFNULL(er.next_operate_time_,NOW()))) / count( er.id_ ) ) as timeValue FROM mb_erp_equipment_eqp_status_records er LEFT JOIN mb_erp_equipment_eqp_basics eb ON er.eqp_id_ = eb.id_ WHERE er.now_status_ = 30 GROUP BY er.eqp_id_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertEqpResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getMeanTimeToRepair() {
		String sql = "SELECT eb.eqp_code_, eb.eqp_name_, count(er.id_) as value, (SUM(TIMESTAMPDIFF(MINUTE,er.create_time,IFNULL(er.next_operate_time_,NOW()))) / count( er.id_ ) ) as timeValue FROM mb_erp_equipment_eqp_status_records er LEFT JOIN mb_erp_equipment_eqp_basics eb ON er.eqp_id_ = eb.id_ WHERE er.now_status_ = 40 GROUP BY er.eqp_id_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertEqpResult(resultList);
	}

	private List<EquipmentReportVO> convertResult(List<Object[]> resultList) {
		ArrayList<EquipmentReportVO> result = new ArrayList<>();
		for (Object[] row : resultList) {
			EquipmentReportVO report = new EquipmentReportVO();
			if (row[0] != null) {
				report.setM((String) row[0]);
			}
			Object value = row[1];
			if (value != null) {
				if (value instanceof BigInteger) {
					BigInteger bigValue = (BigInteger) row[1];
					report.setLValue(bigValue.longValue());
				} else {
					report.setValue((BigDecimal) value);
				}
			}

			result.add(report);
		}
		return result;
	}

	private List<EquipmentReportVO> convertAllResult(List<Object[]> resultList) {
		ArrayList<EquipmentReportVO> result = new ArrayList<>();
		for (Object[] row : resultList) {
			EquipmentReportVO report = new EquipmentReportVO();
			report.setM((String) row[0]);
			if (row[1] != null) {
				BigInteger value = (BigInteger) row[1];
				report.setLValue(value.longValue());
			}
			report.setDValue((BigDecimal) row[2]);
			report.setValue((BigDecimal) row[3]);
			result.add(report);
		}
		return result;
	}

	private List<EquipmentReportVO> convertEqpResult(List<Object[]> resultList) {
		ArrayList<EquipmentReportVO> result = new ArrayList<>();
		for (Object[] row : resultList) {
			EquipmentReportVO report = new EquipmentReportVO();
			if (row[0] != null) {
				report.setEqpCode((String) row[0]);
			}
			if (row[1] != null) {
				report.setEqpName((String) row[1]);
			}
			if (row[2] != null) {
				BigInteger value = (BigInteger) row[2];
				report.setLValue(value.longValue());
			}
			if (row[3] != null) {
				report.setValue((BigDecimal) row[3]);
			}
			result.add(report);
		}
		return result;
	}

	/**
	 * 获取设备开始时间，结束时间和运行时间
	 */
	@Override
	@Transactional(readOnly = true)
	public BigDecimal getMTBF() throws Exception {
		String sql = "SELECT c.EQUIPMENTID, e.EQUIPMENTNAME ,c.ISRESTTIME FROM EQUIPMENT e \n" +
				"JOIN   CALENDAR c \n" +
				"ON e.EQUIPMENTID = c.EQUIPMENTID  \n" +
				"WHERE   e.EQUIPMENTNAME LIKE '%AT%'\n" +
				"AND MONTH(c.STARTEDDATE) = MONTH(GETDATE())  \n" +
				"AND YEAR(c.STARTEDDATE) = YEAR(GETDATE())\n" +
				"AND c.ISRESTTIME = '0'\n" +
				"GROUP BY \n" +
				"c.EQUIPMENTID, e.EQUIPMENTNAME, c.ISRESTTIME ";
//		ArrayList<EquipmentReportVO> result = new ArrayList<>();
//		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		DataGridWrapper dataGridWrapper = dbService.queryTableData(dbInfoId, "", sql, 10000, 1);
//		for (Object[] row : resultList) {
//			EquipmentReportVO report = new EquipmentReportVO();
//			report.setEQUIPMENTID((Integer) row[0]);
////				BigDecimal value = (BigDecimal) row[1];
////				report.setWorkOrder(value.longValue());
//			report.setEQUIPMENTNAME((String) row[1]);
////				report.setSTARTEDDATE((String) row[2]);
////				report.setENDEDDATE((String) row[3]);
////			report.setOneRunTime((Integer) row[2]);
//			report.setOneRunTime(11);
//			result.add(report);
		int totalCount = dataGridWrapper.getTotalCount();
		if(totalCount!= 0){
			//总运行时长
			Integer a = 0;
			a = totalCount * 11;
			BigDecimal allRunTime = new BigDecimal(a);
//        for (EquipmentReportVO vo : result) {
//			BigDecimal bigDecimal = new BigDecimal(vo.getOneRunTime());
//		allRunTime= allRunTime.add(bigDecimal);
//	}
			String sql2 = "SELECT *\n" +
					"FROM MB_ERP_REPAIR   \n" +
					"WHERE MONTH(CREATE_TIME) = MONTH(GETDATE())    \n" +
					"AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
			List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
			int totalCount1 = resultList1==null?0:resultList1.size();
			if (totalCount1 == 0){
				return BigDecimal.ZERO;
			}else {
//		Integer workOrder1 = (Integer)JpaUtil.nativeQuery(sql2).getResultList().get(0);
				BigDecimal workOrder = new BigDecimal(totalCount1);
				BigDecimal mtbf = allRunTime.divide(workOrder,2, RoundingMode.HALF_UP);
				return mtbf;
			}
		}return  BigDecimal.ZERO;
	}

	/**
	 * 设备总数
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public Integer getEquipmentNum() throws Exception {
		String sql1 = "SELECT * FROM MB_ERP_EQUIPMENT_EQP_BASICS WHERE STATUS_ !=40 ";//查询出来除了报废的所以设备 -0
//		String sql1 = "SELECT * FROM MB_ERP_EQUIPMENT_EQP_BASICS WHERE STATUS_ !=40 AND IS_CALIBRATE_ != 1";//查询出来除了报废的所以设备 -0
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int num = resultList==null?0:resultList.size();
		if (num == 0){
			return  0;
		}
//		//找到检定状态的设备
//		String sql2 ="SELECT * FROM MB_ERP_EQUIPMENT_EQP_BASICS WHERE IS_CALIBRATE_  = 1";
//		List resultList2 = JpaUtil.nativeQuery(sql2).getResultList();
//		int num2 = resultList2==null?0:resultList2.size();
//		int num = num1 - num2;
		return num;
	}

	/**
	 *  当月故障设备数
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public Integer getBadEquipmentNum() throws Exception {
		String sql2 = "SELECT DISTINCT TARGET_ID_ FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
		//查询数据库中MB_ERP_REPAIR表中的数据
		List resultList = JpaUtil.nativeQuery(sql2).getResultList();
		//获取MB_ERP_REPAIR表中的总记录数
		int badNUm =resultList==null?0:resultList.size();
		return badNUm;
	}

	@Override
	@Transactional
	public BigDecimal getMTTR() throws Exception {
		//查询数据库中当月的维修工单数
		String sql1 = "SELECT *\n" +
				"FROM MB_ERP_REPAIR   \n" +
				"WHERE MONTH(CREATE_TIME) = MONTH(GETDATE())    \n" +
				"AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
		List<Object[]> resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int orderNum = resultList==null?0:resultList.size();
//		Long totalWorkMinutes = 0L;
		if (orderNum == 0){
			return BigDecimal.ZERO;
		}else {
			String sql2 = "SELECT SUM(WORK_MINUTES_) FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE())  AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
			List<Object> resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
//			for (Object row : resultList1) {
//				BigInteger aa = (BigInteger) row;
//			}
			BigInteger o = (BigInteger) resultList1.get(0);
			Long totalWorkMinutes = o.longValue();
			BigDecimal workMinutes = new BigDecimal(totalWorkMinutes);//总维修时长（分钟）
			BigDecimal workHours = workMinutes.divide(new BigDecimal(60), 2, RoundingMode.HALF_UP);//总维修时长（小时）
			BigDecimal order = new BigDecimal(orderNum);
			BigDecimal mttr = workHours.divide(order,2, RoundingMode.HALF_UP);
			return mttr;
		}

	}


	/**
	 * 设备完好率
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public BigDecimal getEquGood() throws Exception {
		String sql1 = "SELECT * FROM MB_ERP_EQUIPMENT_EQP_BASICS WHERE STATUS_ !=40 AND IS_CALIBRATE_ != 1";//查询出来设备-报废的-检定的设备数
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int goodNum = resultList==null?0:resultList.size();//查询出来设备-报废的-检定的设备数
		if (goodNum == 0){
			return  BigDecimal.ZERO;
		}
		//找到故障设备数中的检定状态的设备
		String sql2 ="SELECT DISTINCT e.basics_code_ FROM MB_ERP_REPAIR m JOIN MB_ERP_EQUIPMENT_EQP_BASICS e on m.target_id_ = e.basics_code_ where (e.IS_CALIBRATE_  = 1 OR e.status_ = 40) AND MONTH(m.CREATE_TIME) = MONTH(GETDATE()) AND YEAR(m.CREATE_TIME) = YEAR(GETDATE())";
		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
		int verBadNum =  resultList1==null?0:resultList1.size();//查询出来设备-报废的-检定的设备数

		//查询当月的故障设备
		String sql3 = "SELECT DISTINCT TARGET_ID_ FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
		//查询数据库中MB_ERP_REPAIR表中的数据
		List resultList2 = JpaUtil.nativeQuery(sql3).getResultList();
		//获取去重的故障的设备数
		int badNUm = resultList2==null?0:resultList2.size();

		//故障设备中去掉检定的设备数
		int lastBadNum = badNUm - verBadNum;
		//设备数中减去故障的数
		int lastGoodNum = goodNum - lastBadNum;

		//计算设备完好率
		BigDecimal lastGood = new BigDecimal(lastGoodNum);
		BigDecimal lastNum = new BigDecimal(goodNum);
		BigDecimal equGood = lastGood.divide(lastNum,2, RoundingMode.HALF_UP).multiply(new BigDecimal("100"));
		return equGood;


	}


	@Override
	@Transactional
	public String getAverageResponseTime() throws Exception {
		//查询数据库中MB_ERP_REPAIR表中的数据
		String sql1 = "SELECT * FROM MB_ERP_REPAIR";
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int orderNum = resultList==null?0:resultList.size();//查询出来工单的数量
		//取响应时长
		String sql2 = "SELECT SUM(DATEDIFF(MINUTE, APPLY_TIME_, ACTUAL_START_DATE_)) AS oneRunTime FROM MB_ERP_REPAIR";
		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
		Integer responseTime = (Integer)resultList1.get(0);
//		//取响应时长
//		List<Map<String, Object>> tableData = dataGridWrapper2.getTableData();
//		Integer responseTime= 0;
//		for (Map<String, Object> map : tableData){
//			Integer time = (Integer)map.get("oneRunTime");
//			if (time !=null){
//				responseTime += time;
//			}else {
//				responseTime += 0;
//			}
//		}
		if (orderNum== 0){
			return "0";
		}else {
			//计算平均响应时长
			double averageResponseTime= (double) responseTime/60/orderNum;
			//保留两位小数
			DecimalFormat df = new DecimalFormat("#.##");
			String result = df.format(averageResponseTime);
			return result;
		}
	}

	@Override
	@Transactional
	public String getAverageMaintenanceTime() throws Exception {
		//查询数据库中MB_ERP_REPAIR表中的数据
		String sql1 = "SELECT * FROM MB_ERP_REPAIR";
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int orderNum = resultList==null?0:resultList.size();
		//取维修时长
		String sql2 = "SELECT SUM(DATEDIFF(MINUTE, ACTUAL_START_DATE_, ACTUAL_END_DATE_)) AS oneRunTime FROM MB_ERP_REPAIR";
		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
		//取维修时长
		Integer repairTime = (Integer)resultList1.get(0);
//		List<Map<String, Object>> tableData = dataGridWrapper2.getTableData();
//		Integer repairTime= 0;
//		for (Map<String, Object> map : tableData){
//			Integer time = (Integer)map.get("oneRunTime");
//			if (time !=null){
//				repairTime += time;
//			}else {
//				repairTime += 0;
//			}
//		}
		if (orderNum == 0){
			return "0";
		}else {
			//计算平均维修时长
			double averageRepairTime= (double) repairTime/60/orderNum;
			//保留两位小数
			DecimalFormat df = new DecimalFormat("#.##");
			String result = df.format(averageRepairTime);
			return result;
		}
	}

	@Override
	@Transactional
	public String getAverageDownTime() throws Exception {
		//查询数据库中MB_ERP_REPAIR表中的数据
		String sql1 = "SELECT * FROM MB_ERP_REPAIR";
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int orderNum = resultList==null?0:resultList.size();
		//取故障时长
		String sql2 = "SELECT SUM(DATEDIFF(MINUTE, APPLY_TIME_, ACTUAL_END_DATE_)) AS oneRunTime FROM MB_ERP_REPAIR";
		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
		//取维修时长
		Integer faultDuration = (Integer)resultList1.get(0);
//		List<Map<String, Object>> tableData = dataGridWrapper2.getTableData();
//		Integer faultDuration= 0;
//		for (Map<String, Object> map : tableData){
//			Integer time = (Integer)map.get("oneRunTime");
//			if (time !=null){
//				faultDuration += time;
//			}else {
//				faultDuration += 0;
//			}
//		}
		if (orderNum ==0){
			return "0";
		}else {
			//计算平均故障时长
			double averageFaultDuration= (double) faultDuration/60/orderNum;
			//保留两位小数
			DecimalFormat df = new DecimalFormat("#.##");
			String result = df.format(averageFaultDuration);
			return result;
		}
	}

	/**
	 *  获取PM计划数量
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public Integer getPMPlanNum() throws Exception {
		String sql = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('1', '2') AND ORDER_STATUS_ IN ('10','20','30','40','45','46','90') ";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		int totalCount = resultList==null?0:resultList.size();
		if (totalCount <= 0) {
			return 0;
		} else {
			return totalCount;
		}
	}

	/**
	 *  获取PM实际数量
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public Integer getPMActualNum() throws Exception {
		String sql = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('1', '2') AND ORDER_STATUS_ IN ('47','48','49','50') ";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		int totalCount = resultList==null?0:resultList.size();
		if (totalCount <= 0) {
			return 0;
		} else {
			return totalCount;
		}
	}

	/**
	 * 定期维护计划数
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public Integer getNormp() throws Exception {
		String sql = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('3', '4','5') AND ORDER_STATUS_ IN ('10','20','30','40','45','46','90')  ";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		int totalCount = resultList==null?0:resultList.size();
		if (totalCount <= 0) {
			return 0;
		} else {
			return totalCount;
		}
	}

	/**
	 * 定期维护实际数
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public Integer getNorma() throws Exception {
		String sql = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('3', '4','5') AND ORDER_STATUS_ IN ('47','48','49','50')";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		int totalCount = resultList==null?0:resultList.size();
		if (totalCount <= 0) {
			return 0;
		} else {
			return totalCount;
		}
	}

	/**
	 *  获取PM完成率
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public String getPMCom() throws Exception {
		//pm已下达保养工单数
		String sql1 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('1', '2') AND ORDER_STATUS_ !=10";
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int actualNum = resultList==null?0:resultList.size();
//		//pm计划数
//		String sql2 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('1', '2')";
//		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
//		int planNum = resultList1==null?0:resultList1.size();
		//pm超时工单数
		String sql3 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('1', '2') \n" +
				"AND ORDER_STATUS_ !=10 \n" +
				"AND over_flag_ =  '1'";
		List resultList3 = JpaUtil.nativeQuery(sql3).getResultList();
		int timeOut = resultList3==null?0:resultList3.size();
		if (actualNum == 0){
			return "0";
		}else {
			//PM完成率
			double pmCom = (double) (actualNum-timeOut) / actualNum * 100;

			//保留两位小数
			DecimalFormat df = new DecimalFormat("#.##");
			String result = df.format(pmCom);
			return result;
		}

	}

	/**
	 * 定期维护完成率
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public String getNormCom() throws Exception {
		//实际数
		String sql1 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('3', '4','5') AND ORDER_STATUS_ = '49'";
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int actualNum = resultList==null?0:resultList.size();
		//计划数
		String sql2 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('3', '4','5')";
		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
		int planNum = resultList1==null?0:resultList1.size();
		//定期维护完成率
		if (planNum == 0){
			return "0";
		}else {
			double normCom = (double) actualNum / planNum * 100;
			//保留两位小数
			DecimalFormat df = new DecimalFormat("#.##");
			String result = df.format(normCom);
			return result;
		}
	}

	/**
	 * 按时完成率
	 * @return
	 * @throws Exception
	 */
	@Override
	@Transactional
	public String getOnTimeCom() throws Exception {
		//下达单子
		String sql1 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('3', '4','5') AND ORDER_STATUS_ !=10";
		List resultList = JpaUtil.nativeQuery(sql1).getResultList();
		int released = resultList==null?0:resultList.size();
		//超时
		String sql2 = "SELECT * FROM MB_ERP_EQUIPMENT_UPKEEP_TASK WHERE MONTH(PLAN_START_TIME_) = MONTH(GETDATE()) AND YEAR(PLAN_START_TIME_) = YEAR(GETDATE()) AND UPKEEP_TYPE_ IN ('3', '4','5') AND ORDER_STATUS_ !=10 AND OVER_FLAG_ = '1'";
		List resultList1 = JpaUtil.nativeQuery(sql2).getResultList();
		int timeout = resultList1==null?0:resultList1.size();
		//按时完成率
		if (released == 0){
			return "0";
		}else {
			double finishOnTimeCom = (double) (released - timeout) / released * 100;
			//保留两位小数
			DecimalFormat df = new DecimalFormat("#.##");
			String result = df.format(finishOnTimeCom);
			return result;
		}

	}

/**------------------------------------------------当月故障趋势------------------------------------------------------------*/

     /**
	  * 当月故障趋势
	  * @return
	  */
    @Override
	@Transactional
    public List<EquipmentReportVO> getFaultTrend() throws Exception {
//		//查询出当月的维修工单
//		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
//		List<Map<String, Object>> tableData = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 3).getTableData();
//		for (Map<String, Object> map : tableData){
//			Object o = map.get("APPLY_TIME_");
//
//		}
		/**
		 * 查询出当月的维修工单
		 */
//		String sql = "SELECT DATEPART(DAY, APPLY_TIME_) AS Day, COUNT(*) AS Count    \n" +
//				"                      FROM MB_ERP_REPAIR \n" +
//				"                      WHERE APPLY_TIME_ >= DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE()) \n" +
//				"                      AND APPLY_TIME_ < DATEADD(DAY, -DAY(GETDATE()) + 1, DATEADD(MONTH, 1, GETDATE())) \n" +
//				"                      GROUP BY DATEPART(DAY, APPLY_TIME_)  \n" +
//				"                      ORDER BY Day";
//		return jdbcTemplate.query(sql, (rs, num) -> {
//			EquipmentReportVO vo = new EquipmentReportVO();
//			vo.setDay(rs.getInt("Day"));
//			vo.setCount(rs.getInt("Count"));
//			System.out.println(vo.toString());
//			return vo;
//		});
		/**
		 * 查询出当天的维修工单和之前29天的维修工单， 共计30天的
		 */
//		String sql ="WITH DateRange AS (  \n" +
//				"\t\t\tSELECT CAST(GETDATE() AS DATE) AS DateValue  \n" +
//				"\t\t\tUNION ALL  \n" +
//				"\t\t\tSELECT DATEADD(DAY, -1, DateValue)  \n" +
//				"\t\t\tFROM DateRange  \n" +
//				"\t\t\tWHERE DateValue > DATEADD(DAY, -29, CAST(GETDATE() AS DATE)) )\n" +
//				"\t\t\t\n" +
//				"\t\t\tSELECT  \n" +
//				"\t\t\tdr.DateValue AS Day,  \n" +
//				"\t\t\tISNULL(COUNT(mr.APPLY_TIME_), 0) AS Count \n" +
////				"\t\t\tISNULL(SUM(mr.fault_time_), 0) AS FaultTimeSum \n" +
//				"\t\t\tFROM \n" +
//				"\t\t\tDateRange dr \n" +
//				"\t\t\tLEFT JOIN  \n" +
//				"\t\t\tMB_ERP_REPAIR mr \n" +
//				"\t\t\tON  \n" +
//				"\t\t\tmr.APPLY_TIME_ >= dr.DateValue AND mr.APPLY_TIME_ < DATEADD(DAY, 1, dr.DateValue)  \n" +
//				"\t\t\tWHERE  \n" +
//				"\t\t\tdr.DateValue BETWEEN DATEADD(DAY, -29, CAST(GETDATE() AS DATE)) AND CAST(GETDATE() AS DATE)  \n" +
//				"\t\t\tGROUP BY  \n" +
//				"\t\t\tdr.DateValue  \n" +
//				"\t\t\tORDER BY \n" +
//				"\t\t\tdr.DateValue;\n" +
//				"\t\t\t";
		String sql ="WITH DateRange AS (\n" +
				"\t\t\t\tSELECT CAST\n" +
				"\t\t\t\t( GETDATE( ) AS DATE ) AS DateValue UNION ALL\n" +
				"\t\t\t\tSELECT DATEADD( DAY, - 1, DateValue ) \n" +
				"\t\t\t\tFROM DateRange \n" +
				"\t\t\t\tWHERE DateValue > DATEADD( DAY, - 29, CAST ( GETDATE( ) AS DATE ) ) ),\n" +
				"\t\t\t\tCountRepair AS (\n" +
				"\t\t\t\tSELECT dr.DateValue AS DAY,\n" +
				"\t\t\t\tISNULL( COUNT ( mr.APPLY_TIME_ ), 0 ) AS RepairCount \n" +
				"\t\t\t\tFROM DateRange dr\n" +
				"\t\t\t\tLEFT JOIN MB_ERP_REPAIR mr ON mr.APPLY_TIME_ >= dr.DateValue \n" +
				"\t\t\t\tAND mr.APPLY_TIME_ < DATEADD( DAY, 1, dr.DateValue ) \n" +
				"\t\t\t\tAND mr.BIZ_TYPE_ = 'CI' \n" +
				"\t\t\t\tAND mr.REPAIR_STATUS_ != 90 \n" +
				"\t\t\t\tWHERE dr.DateValue BETWEEN DATEADD( DAY, - 29, CAST ( GETDATE( ) AS DATE ) ) \n" +
				"\t\t\t\tAND CAST ( GETDATE( ) AS DATE ) \n" +
				"\t\t\t\tGROUP BY dr.DateValue ),\n" +
				"\t\t\t\tAverageFaultTime AS (\n" +
				"\t\t\t\tSELECT dr.DateValue AS DAY,\n" +
				"\t\t\t\tISNULL( SUM ( mr.fault_time_ ), 0 ) AS AverageFaultTime \n" +
				"\t\t\t\tFROM\n" +
				"\t\t\t\tDateRange dr\n" +
				"\t\t\t\tLEFT JOIN MB_ERP_REPAIR mr ON mr.APPLY_TIME_ >= dr.DateValue \n" +
				"\t\t\t\tAND mr.APPLY_TIME_ < DATEADD( DAY, 1, dr.DateValue ) \n" +
				"\t\t\t\tAND mr.BIZ_TYPE_ = 'CI' \n" +
				"\t\t\t\tAND mr.REPAIR_STATUS_ != 90 \n" +
				"\t\t\t\tWHERE dr.DateValue BETWEEN DATEADD( DAY, - 29, CAST ( GETDATE( ) AS DATE ) ) \n" +
				"\t\t\t\tAND CAST ( GETDATE( ) AS DATE ) \n" +
				"\t\t\t\tGROUP BY  dr.DateValue ) \n" +
				"\t\t\t\tSELECT c.Day,\n" +
				"\t\t\t\tc.RepairCount,\n" +
				"\t\t\t\ta.AverageFaultTime \n" +
				"\t\t\t\tFROM  CountRepair c\n" +
				"\t\t\t\tLEFT JOIN AverageFaultTime a ON c.Day = a.Day \n" +
				"\t\t\t\tORDER BY\n" +
				"\t\t\t\tc.Day";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		      return resultList;

	}

	@Override
	public Integer getAssemble() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getAllocation() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND repair_status_ = 10";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getToBeRepaired() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND repair_status_ = 20";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getInMaintenance() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND repair_status_ = 30";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getSpareParts() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH (CREATE_TIME) = MONTH (GETDATE()) AND YEAR (CREATE_TIME) = YEAR (GETDATE()) AND order_status_ = 3";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOutsourcing() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH (CREATE_TIME) = MONTH (GETDATE()) AND YEAR (CREATE_TIME) = YEAR (GETDATE()) AND order_status_ = 4";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getToBeSubmitted() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND repair_status_ = 31";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getToBeAccepted() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND repair_status_ = 32";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getToBeEvaluated() throws Exception {
		String sql = "SELECT * FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND repair_status_ = 40";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}
/*================================================保养===========================================================*/
@Override
public Integer getOrderStatusAssemble() throws Exception {
	String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE())";
	DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
	int totalCount = dataGridWrapper.getTotalCount();
	if (totalCount <= 0){
		return 0;
	}else {
		return totalCount;
	}
}

	@Override
	public Integer getOrderStatusAllocation() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND order_status_ = 20";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusMaintenance() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND order_status_ = 30";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusUnderMaintenance() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND order_status_ = 40";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusSpareParts() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND exec_status_ = 3";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusShelve() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND exec_status_ = 2";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusToBeSubmitted() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND order_status_ = 46";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusToBeAccepted() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND order_status_ = 47";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}

	@Override
	public Integer getOrderStatusToBeEvaluate() throws Exception {
		String sql = "SELECT * FROM mb_erp_equipment_upkeep_task WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE()) AND order_status_ = 48";
		DataGridWrapper dataGridWrapper = dbService.queryTableData(DbConstants.DEFAULTDATASOURCE, "", sql, 10000, 1);
		int totalCount = dataGridWrapper.getTotalCount();
		if (totalCount <= 0){
			return 0;
		}else {
			return totalCount;
		}
	}


	@Override
	@Transactional
	public Map<String, Integer> getMaintenanceDetails() {
		String sql = "SELECT 'assemble' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE MONTH(plan_start_time_) = MONTH(GETDATE()) AND YEAR(plan_start_time_) = YEAR(GETDATE())\n" +
				"union all\n" +
				"SELECT 'allocation' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  order_status_ = 20\n" +
				"union all\n" +
				"SELECT 'maintenance' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  order_status_ = 30\n" +
				"union all\n" +
				"SELECT 'underMaintenance' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  order_status_ = 40\n" +
				"union all\n" +
				"SELECT 'spareParts' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  exec_status_ = 3\n" +
				"union all\n" +
				"SELECT 'shelve' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  exec_status_ = 2\n" +
				"union all\n" +
				"SELECT 'toBeSubmitted' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  order_status_ = 46\n" +
				"union all\n" +
				"SELECT 'toBeAccepted' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  order_status_ = 47\n" +
				"union all\n" +
				"SELECT 'toBeEvaluated' as QueryName, COUNT(*) as TotalCount FROM mb_erp_equipment_upkeep_task WHERE  order_status_ = 48";
		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		HashMap<String, Integer> map = new HashMap<>();
		for (Object[] row : resultList) {
			String describe = (String) row[0];
			Integer number = (Integer) row[1];
			map.put(describe, number);
		}
		return map;
	}

	@Override
	@Transactional
	public Map<String, Integer> getRepairDetails() {
		String sql = "SELECT 'assemble' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE MONTH(CREATE_TIME) = MONTH(GETDATE()) AND YEAR(CREATE_TIME) = YEAR(GETDATE())\n" +
				"UNION ALL  \n" +
				"SELECT 'allocation' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 10  \n" +
				"UNION ALL  \n" +
				"SELECT 'toBeRepaired' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 20\n" +
				"UNION ALL  \n" +
				"SELECT 'inMaintenance' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 30\n" +
				"UNION ALL  \n" +
				"SELECT 'spareParts' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE order_status_ = 3\n" +
				"UNION ALL  \n" +
				"SELECT 'outsourcing' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE order_status_ = 4\n" +
				"UNION ALL  \n" +
				"SELECT 'toBeSubmitted' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 31\n" +
				"UNION ALL  \n" +
				"SELECT 'toBeAccepted' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 32\n" +
				"UNION ALL  \n" +
				"SELECT 'toBeEvaluated' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 40\n"+
				"UNION ALL  \n" +
				"SELECT 'stopRepaired' as describe, count(*) as number FROM MB_ERP_REPAIR WHERE repair_status_ = 35" ;
		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		HashMap<String, Integer> map = new HashMap<>();
		for (Object[] row : resultList) {
			String describe = (String) row[0];
			Integer number = (Integer) row[1];
			map.put(describe, number);
		}
		return map;
	}

	@Override
	@Transactional
	public String getFirstLevelCode(String code) {
		Dept category = JpaUtil.linq(Dept.class).equal("deptCode", code).findOne();
		if (category.getFaDeptId() ==null|| StringUtils.isBlank(category.getFaDeptId())){
			return category.getDeptCode();
		}
		Dept pid = JpaUtil.getOne(Dept.class, category.getFaDeptId());
		return getFirstLevelCode(pid.getDeptCode());
	}

	@Override
	@Transactional
	public String getSecondLevelCode(String firstUseDeptId, String useDeptId) {
		Dept category = JpaUtil.linq(Dept.class).equal("deptCode", useDeptId).findOne();
		if (category.getFaDeptId() == null || category.getFaDeptId().equals(firstUseDeptId)){
			return category.getDeptCode();
		}
		Dept pid = JpaUtil.getOne(Dept.class, category.getFaDeptId());
		return getSecondLevelCode(firstUseDeptId,pid.getDeptCode());
	}

	@Override
	@Transactional
	public String getThirdLevelCode(String secondUseDeptId, String useDeptId) {
		Dept category = JpaUtil.linq(Dept.class).equal("deptCode", useDeptId).findOne();
		if ( category.getFaDeptId() == null||category.getFaDeptId().equals(secondUseDeptId)){
			return category.getDeptCode();
		}
		Dept pid = JpaUtil.getOne(Dept.class, category.getFaDeptId());
		return getSecondLevelCode(secondUseDeptId,pid.getDeptCode());
	}

	@Override
	@Transactional
	public Dept getWithBranchByCode(String code) {
		Map<String, List<Dept>> childrenMap = new HashMap<String, List<Dept>>();
		List<Dept> depts = JpaUtil.linq(Dept.class).list();
		Dept category = null;
		for (Dept per : depts) {
			if (childrenMap.containsKey(per.getId())) {
				per.setChildren(childrenMap.get(per.getId()));
			} else {
				per.setChildren(new ArrayList<Dept>());
				childrenMap.put(per.getId(), per.getChildren());
			}

			if (per.getFaDeptId() != null) {
				List<Dept> children;
				if (childrenMap.containsKey(per.getFaDeptId())) {
					children = childrenMap.get(per.getFaDeptId());
				} else {
					children = new ArrayList<Dept>();
					childrenMap.put(per.getFaDeptId(), children);
				}
				children.add(per);
			}
			if (per.getDeptCode().equals(code)){
				category = per;
			}
		}
		return category;
	}

	@Override
	@Transactional
	public List<String> getChildrenDept(String firstLevelCode) { //查询当前部门的所有的子级
	//查询当前工段下的所有的孩子和孙子
		List<String> childrenDept = new ArrayList<>();
//		List<Dept> list = JpaUtil.linq(Dept.class).list();
		Dept withBranchByCode = this.getWithBranchByCode(firstLevelCode);
//		Dept one = JpaUtil.linq(Dept.class).equal("deptCode", firstLevelCode).findOne();
		if (withBranchByCode !=null){
			getDeptCode(withBranchByCode,childrenDept);
		}
        return childrenDept;
    }

	private void getDeptCode(Dept dept,List<String> childrenDept){
	    if (dept.getChildren() !=null && dept.getChildren().size()>0){
            for (Dept per : dept.getChildren()) {
                childrenDept.add(per.getDeptCode());
                getDeptCode(per,childrenDept);
            }
        }
    }


}

//	/**
//	 * 获取前30天的类型为CI的停机时长
//	 * @return
//	 */
//	@Override
//	@Transactional
//	public Result<?> getDowntime() {
//		String sql="WITH DateRange AS (   \n" +
//				"    SELECT CAST(GETDATE() AS DATE) AS DateValue    \n" +
//				"    UNION ALL    \n" +
//				"    SELECT DATEADD(DAY, -1, DateValue)   \n" +
//				"    FROM DateRange   \n" +
//				"    WHERE DateValue > DATEADD(DAY, -29, CAST(GETDATE() AS DATE))   \n" +
//				")  \n" +
//				"  \n" +
//				"SELECT   \n" +
//				"    dr.DateValue AS Day,    \n" +
//				"    ISNULL(SUM(mr.fault_time_), 0) AS FaultTimeSum   \n" +
//				"FROM   \n" +
//				"    DateRange dr   \n" +
//				"LEFT JOIN    \n" +
//				"    MB_ERP_REPAIR mr   \n" +
//				"ON    \n" +
//				"    mr.APPLY_TIME_ >= dr.DateValue   \n" +
//				"    AND mr.APPLY_TIME_ < DATEADD(DAY, 1, dr.DateValue)  \n" +
//				"    AND mr.BIZ_TYPE_ = 'CI'  \n" +
//				"WHERE    \n" +
//				"    dr.DateValue BETWEEN DATEADD(DAY, -29, CAST(GETDATE() AS DATE)) AND CAST(GETDATE() AS DATE)  \n" +
//				"  \n" +
//				"GROUP BY    \n" +
//				"    dr.DateValue    \n" +
//				"ORDER BY   \n" +
//				"    dr.DateValue";
//		JpaUtil.nativeQuery(sql)
//		return null;
//	}
//}
